package com.sanqi.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.web.multipart.MultipartFile;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class PoiExcelUtil {
    
    public static Workbook getWorkBook(MultipartFile file) {
        if(null == file) {
            return null;
        }
        String fileName = org.apache.commons.io.FilenameUtils.getName(file.getOriginalFilename());
        Workbook workbook = null;
        if(StrUtil.endWith(fileName, ".xlsx")) {
            try {
                workbook = new XSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
                log.error(e.getMessage(), e);
            }
        }
        if(StrUtil.endWith(fileName, ".xls")) {
            try {
                workbook = new HSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return workbook;
    }
    
    public static Workbook getWorkBook(File file) {
        if(null == file) {
            return null;
        }
        String fileName = file.getName();
        Workbook workbook = null;
        if(StrUtil.endWith(fileName, ".xlsx")) {
            try {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        if(StrUtil.endWith(fileName, ".xls")) {
            try {
                workbook = new HSSFWorkbook(new FileInputStream(file));
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return workbook;
    }
    
    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){

        if(cell == null) {
            return "";
        }

        if(cell.getCellType() == CellType.STRING){

            return cell.getStringCellValue();

        }else if(cell.getCellType() == CellType.BOOLEAN){

            return String.valueOf(cell.getBooleanCellValue());

        }else if(cell.getCellType() ==  CellType.FORMULA){

            return cell.getCellFormula() ;

        }else if(cell.getCellType() == CellType.NUMERIC){

            return String.valueOf(cell.getNumericCellValue());

        }
        return "";
    }
    
    /**
     * @Description 将excel的日期数字转化为Date类
     * @param dVal
     * @return
     * Create at: 2025-10-28 10:38:09
     * @author: duany
     * Revision:
     *    2025-10-28 10:38:09 - first revision by duany
     *        
     */
    public static Date excelDateNumToDate(Double dVal) {
        if(null == dVal) {
            return null;
        }
        Date tDate = new Date();
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000));

        return DateUtil.beginOfDay(tDate);
    }
    
    public static XSSFWorkbook getXSSFWorkbook(File file) {
        if(null == file) {
            return null;
        }
        String fileName = file.getName();
        XSSFWorkbook workbook = null;
        if(StrUtil.endWith(fileName, ".xlsx")) {
            try {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return workbook;
    }
    
    /**
     * @Description 创建单元格样式
     * @param wb Excel文件的workbook
     * @param border 单元格边框
     * @param foregroundColor 背景色
     * @param font 字体样式
     * @param horizontalAlignment 水平对齐方式
     * @param verticalAlignment 垂直对齐方式
     * @param wrapText 是否自动换行
     * @return
     * Create at: 2025-10-30 14:25:28
     * @author: duany
     * Revision:
     *    2025-10-30 14:25:28 - first revision by duany
     *        
     */
    public static CellStyle getCellStyle(Workbook wb, BorderStyle border, Short foregroundColor, Font font,
            HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, Boolean wrapText) {
        CellStyle cellStyle = wb.createCellStyle();
        if (null != border) {
            cellStyle.setBorderBottom(border);
            cellStyle.setBorderLeft(border);
            cellStyle.setBorderRight(border);
            cellStyle.setBorderTop(border);
        }
        if (null != foregroundColor) {
            cellStyle.setFillForegroundColor(foregroundColor.shortValue());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        if (null != font) {
            cellStyle.setFont(font);
        }
        if (horizontalAlignment != null) {
            cellStyle.setAlignment(horizontalAlignment);// 水平
        }
        if (null != verticalAlignment) {
            cellStyle.setVerticalAlignment(verticalAlignment);// 垂直
        }
        if(null != wrapText) {
            cellStyle.setWrapText(wrapText.booleanValue());
        }

        return cellStyle;
    }
    
    /**
     * @Description 创建字体样式
     * @param wb Excel文件的workbook
     * @param fontName 字体名称
     * @param blod 是否加粗
     * @param color 字体颜色
     * @param fontHeight 字体大小（像素）
     * @return
     * Create at: 2025-10-30 14:32:00
     * @author: duany
     * Revision:
     *    2025-10-30 14:32:00 - first revision by duany
     *        
     */
    public static Font getFont(Workbook wb, String fontName, Boolean blod, Short color, Short fontHeight) {
        Font font = wb.createFont();
        if(null != fontName) {
            font.setFontName(fontName);
        }
        if(null != blod) {
            font.setBold(blod.booleanValue());
        }
        if(null != color) {
            font.setColor(color.shortValue());
        }
        if(null != fontHeight) {
            font.setFontHeightInPoints(fontHeight.shortValue());
        }
        return font;
    }
    
    public static void setStringCellValue(Cell cell, String cellValue, CellStyle cellStyle) {
        if(null != cellStyle) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(cellValue);
    }
    
    public static void setNumberCellValue(Cell cell, BigDecimal cellValue, CellStyle cellStyle) {
        if(null != cellStyle) {
            cell.setCellStyle(cellStyle);
        }
        if(cellValue != null) {
            cell.setCellValue(cellValue.doubleValue());
        }
    }
    
    public static void setNumberCellValue(Cell cell, Double cellValue, CellStyle cellStyle) {
        if(null != cellStyle) {
            cell.setCellStyle(cellStyle);
        }
        if(cellValue != null) {
            cell.setCellValue(cellValue.doubleValue());
        }
    }
    
    /**
     * @Description 创建合并单元格对象
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param border
     * Create at: 2025-10-30 15:01:58
     * @author: duany
     * Revision:
     *    2025-10-30 15:01:58 - first revision by duany
     *        
     */
    public static void getCellRangeAddress(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, BorderStyle border) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); // 创建合并区域对象
        sheet.addMergedRegion(cellRangeAddress);// 添加合并区域到Sheet中
        //是否添加边框
        if(border != null) {
            RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
        }
    }
}
